import tempfile
import frappe
from frappe import _
from frappe.utils import strip_html
from frappe.translate import print_language
import os, json, re, openpyxl
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter
from io import BytesIO
from copy import copy


logged_error = set()    #避免记录重复错误，超量记录错误
    
@frappe.whitelist()
def export_data(doc, template_file, template_name='', export_file_name='', language=None):
    try:
        if isinstance(doc, str):
            doc = frappe.get_doc(json.loads(doc))

        bench_path = frappe.utils.get_bench_path()
        template_file = template_file if template_file[:9] =='/private/' else f"/public{template_file}"
        path=f"{bench_path}/sites/{frappe.local.site}{template_file}"
        if path[-5:] == '.docx':
            temp_file = process_docxtpl(path, doc)
        else:
            temp_file = openpyxl.load_workbook(filename = path)
            for ws in temp_file.worksheets:
                process_worksheet(ws, doc, language, bench_path)
        in_memory_file = BytesIO()
        temp_file.save(in_memory_file)

        file_bytes = in_memory_file.getvalue()
        filename = f'{template_name}_{doc.name}.{path[-4:]}'
        if export_file_name:
            try:
                filename = frappe.render_template(export_file_name, {'doc':doc, 'template_name':template_name, 'path': path})
            except Exception as e:                
                frappe.msgprint(_("Export file name incorrect, error message {0}, default filename used instead").format(str(e)))

        # #转换成pdf
        # if path[-5:] == '.docx':
        #     file_bytes = docx_to_pdf(file_bytes)
        #     filename = f'{template_name}_{doc.name}.pdf'

        frappe.response['filename'] = filename
        frappe.response['filecontent'] = file_bytes
        frappe.response['type'] = 'binary'
    except Exception as e:        
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("export excel open template file error", traceback)
        frappe.msgprint(f"因错误： {str(e)} 单据未能正常导出，请联络管理员查看错误日志(Error Log)了解详情")   
 
def docx_to_pdf(docx_bytes):
    from docx import Document
    from html2pdf import converter

    try:
        doc = Document(docx_bytes)
        # Convert docx to HTML
        html = doc.styles.default_font.element.xml
        for paragraph in doc.paragraphs:
            html += paragraph.element.xml
        for table in doc.tables:
            html += table.element.xml

        # Create a temporary HTML file
        with tempfile.NamedTemporaryFile(suffix='.html', delete=False) as html_file:
            tmp_html_path = html_file.name 
            html_file.write(html.encode('utf-8'))

        # Convert HTML to PDF
        with tempfile.NamedTemporaryFile(suffix='.pdf', delete=False) as pdf_file:
            tmp_pdf_path = pdf_file.name 
            converter.convert(html_file.name, pdf_file.name)

        with open(tmp_pdf_path, "rb") as f:
            pdf_bytes = f.read()
    except (IOError, docx.DocumentError):
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("export excel open template file error", traceback)        
    finally:
        os.remove(tmp_html_path)  
        os.remove(tmp_pdf_path)  

    return pdf_bytes

def process_docxtpl(template_file_name, doc, language=None, bench_path=None):
    from docxtpl import DocxTemplate

    tpl = DocxTemplate(template_file_name)
    #模板中支持{{字段名}} 或 {{doc.字段名}} 访问字段变量值
    context = doc.as_dict()
    context['doc'] = doc
    tpl.render(context, frappe.get_jenv())

    return tpl

def process_worksheet(ws, doc, language=None, bench_path=None):
    max_row, max_column = find_max_data_row_and_column(ws)
    #避免处理太多无效数据格
    max_row = max_row if max_row < 200 else 200
    max_column = max_column if max_column < 50 else 50
    rows, row = [], frappe._dict()
    rows_count, cur_row_index= 0, -1
    i = 1   # row index
    with print_language(language):
        while i<= max_row:
            for j in range(1, max_column+1):
                if j==1:  #明细行第1列批注中设置子表字段,类似这样 rows=doc.items
                    comment = ws.cell(row=i, column=j).comment                
                    if comment:
                        comment = comment.text
                        if comment[:4] == "row=":
                            #这里不能用render_template因为其返回的是文本串，不是子表变量
                            rows = frappe.safe_eval(comment[4:], None, {"doc": doc})
                            ws.cell(row=i, column=j).comment.text = ''    #清空
                            rows_count = len(rows) 
                            if rows_count>1:
                                num_rows_to_insert = rows_count - 1
                                #插入在当前行之前，为了实现在当前行之后插入，设当前行为i+1
                                insert_rows_with_copy(ws, i+1, num_rows_to_insert)
                                max_row += num_rows_to_insert
                # j==1表示按行来处理row的动态切换                    
                if j==1 and rows and rows_count and cur_row_index < rows_count:
                    cur_row_index += 1
                    row = rows[cur_row_index]

                text = str(ws.cell(row=i, column=j).value or '')
                if text and has_variable_string(text):
                    try:
                        value = frappe.render_template(text, {'doc':doc, 'row':row})                        
                        if value and value[0] == "/" and value[-4:] in ['.jpg','.png']:
                            #公开文件路径中要加public,私有文件不用加
                            file = value if value[:9]=='/private/' else f'/public{value}'
                            path = f"{bench_path}/sites/{frappe.local.site}{file}"
                            add_image(ws, i, j, path)
                            ws.cell(row=i, column=j).value = ''
                        else:
                            value = strip_html(value or '').replace('None', '')
                            ws.cell(row=i, column=j).value = text if "{{ no such element:" in value else value
                    except:
                        if not text in logged_error and len(logged_error) < 10:
                            logged_error.add(text)
                            traceback = frappe.get_traceback(with_context=True)
                            frappe.log_error("export excel render template error", traceback)                    

            #清空明细行相关变量
            if rows and rows_count and (cur_row_index + 1) >= rows_count:
                rows_count, cur_row_index = 0, -1
                rows, row =[], frappe._dict()            
            i += 1                

def add_image(ws, row, column, path, width=0, height=0):
    try:        
        img = Image(path)
        if width and height:
            img.width = width
            img.height = height
        column_letter = get_column_letter(column)
        img.anchor = f"{column_letter}{row}"   #'A1'
        ws.add_image(img)
    except:
        if not path in logged_error and len(logged_error) < 10:
            logged_error.add(path)
            traceback = frappe.get_traceback(with_context=True)
            frappe.log_error("export excel add image error", traceback)         

compiled_pattern = re.compile(r'\{\{(?=.*?\}\})')

def has_variable_string(s):
    """at least one occurance of {{变量名}}"""

    if isinstance(s, str):
        return bool(compiled_pattern.search(s))  

def copy_style(src_cell, dest_cell):
    if src_cell.style:    
        if src_cell.font:  
            dest_cell.font = src_cell.font.copy()  
        if src_cell.border:  
            dest_cell.border = src_cell.border.copy()  
        if src_cell.fill:  
            dest_cell.fill = src_cell.fill.copy()
        if src_cell.alignment:  
            dest_cell.alignment = src_cell.alignment.copy()

def insert_rows_with_copy(ws, row_index, num_rows_to_insert):
    """
    Inserts multiple rows at the specified row index and copies the content of the row above.
    """

    #https://stackoverflow.com/questions/51620536/openpyxl-adding-new-rows-in-excel-file-with-merged-cell-existing
    #处理插入行底下行合并单元格整体下移
    merged_cells_range = ws.merged_cells.ranges
    for merged_cell in merged_cells_range:
        if merged_cell.min_row >= row_index:
            merged_cell.shift(0, num_rows_to_insert)

    source_row = ws[row_index-1]
    ws.insert_rows(row_index, num_rows_to_insert)
    
    for i in range(num_rows_to_insert):
        target_row = row_index + i        
        for col in source_row:
            column = col.column
            ws.cell(row=target_row, column=column).value = col.value
            copy_style(ws.cell(row=row_index-1, column=column), ws.cell(row=target_row, column=column))        
        copy_merged_cells(ws, row_index-1, target_row)

def clear_merged_cells_on_row(worksheet, row_number):

    merged_cells_copy = list(worksheet.merged_cells.ranges)
    for merged_cell in merged_cells_copy:
        min_row, max_row = merged_cell.min_row, merged_cell.max_row
        if min_row == row_number:
            worksheet.unmerge_cells(str(merged_cell))

def copy_merged_cells(worksheet, source_row, target_row):
    
    merged_cells_copy = list(worksheet.merged_cells.ranges)
    for merged_cell in merged_cells_copy:
        min_col, max_col, min_row, max_row = merged_cell.min_col, merged_cell.max_col, merged_cell.min_row, merged_cell.max_row
        if min_row == source_row:
            new_merged_cell = f"{get_column_letter(min_col)}{target_row}:{get_column_letter(max_col)}{target_row}"
            worksheet.merge_cells(new_merged_cell)

def find_max_data_row_and_column(ws):
    """最多1000行，100列"""

    max_row = 0  
    max_column = 0  
    for row in ws.iter_rows(min_row=1, max_col=100, max_row=1000):  
        for cell in row:  
            if cell.value and (isinstance(cell.value, str) and cell.value.strip() != '' or not isinstance(cell.value, str)):                  
                max_row = max(max_row, cell.row)  
                max_column = max(max_column, cell.column)  
    return max_row, max_column

@frappe.whitelist()
def get_export_excel_template():
    return frappe.get_all("Excel Export Template",
        filters = {'template_file': ['is','set']},
        fields = ['template_name','template_file', 'export_file_name', 'is_multi_language', 'reference_doctype']
    )